﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ReadRelatedEntities_old2]
	-- Add the parameters for the stored procedure here
	@Entity1ID nvarchar(100),
	@Entity1Type nvarchar(255),
	@SRNameWithDir nvarchar(100) = null,
	-- Optionally limits the search to one particular relationship and direction
	@ReadDirect int=null,
	@ReadChild int=null,
	@WriteDirect int=null,
	@WriteChild int=null,
	@SD datetime = null,
	@ED datetime = null,
	@TD nvarchar(100) = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- SET Filter Dates if provided or TimeDiv is provided
	SET @SD=COALESCE(dbo.GetTimeDivDate(@TD,'START'),@SD)
	SET @ED=COALESCE(dbo.GetTimeDivDate(@TD,'END'),@ED)
	
	-- Get list of Valid possible relationships
	-- FOR THIS PURPOSE, DOUBLE CHECK THAT THE Relationship has at least basic direct read
	DECLARE RelationshipCursor cursor for
		SELECT * FROM GetEntityRelationships(@Entity1Type,@ReadChild, @WriteChild)
			WHERE ((@SRNameWithDir is null OR @SRNameWithDir = SRNameWithDir) AND (E1_Read_Direct_E2 > 0))

    CREATE TABLE #EntityRelationships (
		Entity1ID nvarchar(100),
		Entity1Type nvarchar(100),
		Entity1Name nvarchar(255),
		SRNameWithDir nvarchar(100),
		RelationshipName nvarchar(50),
		SuperRelationship nvarchar(20),
		Entity2ID nvarchar(100), 
		Entity2Type nvarchar(100),
		Entity2Name nvarchar(255),
		StartDate datetime, 
		EndDate datetime,
		E1_ReadDirect_E2 int,
		E1_ReadChild_E2 int,
		E1_WriteDirect_E2 int,
		E1_WriteChild_E2 int
	)
	DECLARE @NEXTREL nvarchar(100) -- THE CALLED RELATIONSHIP WITH DIRECTION
	DECLARE @DIRECTION nvarchar(15)  -- THE DIRECTION part of the CALLED RELATIONSHIP
	DECLARE @BuildSQL nvarchar(max)
	DECLARE @FriendlyID nvarchar(255)
	DECLARE @SuperRelationship nvarchar(12), @RD int, @RC int, @WD int, @WC int
	DECLARE @E1Type nvarchar(100), @E2Type nvarchar(100), @RelName nvarchar(50)

	-- Walk through the list of valid Relationships:
	Open RelationshipCursor
	Fetch NEXT FROM RelationshipCursor INTO  @NEXTREL, @RelName, @SuperRelationship, @E1Type, @E2Type, @RD, @RC, @WD, @WC
	WHILE @@FETCH_STATUS = 0 BEGIN
			SET @DIRECTION = dbo.fn_GetRelOrDir(@NEXTREL,'Dir')

			SET @BuildSQL = 'SELECT Entity1ID, "' + @E1Type + '" AS Entity1Type, Entity1Name, ' +
						' ShortRelationshipName+".' + @DIRECTION + '" as SRNameWithDir, RelationshipName, ' +
						' SuperRelationship, Entity2ID, "' + @E2Type + '" As Entity2Type, Entity2Name,' +
						' StartDate, EndDate, ' + CAST(@RD AS NVARCHAR) + ' AS RD, ' + CAST(@RC AS NVARCHAR) +
						' AS RC, ' + CAST(@WD AS NVARCHAR) + ' AS WD, ' + CAST(@WC AS NVARCHAR) + ' AS WC ' +
						' FROM [{' + @NEXTREL + ' R} WHERE (Entity1ID = "' + @Entity1ID + '") ] '

				
				PRINT @NEXTREL			 
				
				SET @BuildSQL = 'INSERT INTO #EntityRelationships(Entity1ID,Entity1Type,Entity1Name,
							SRNameWithDir, RelationshipName, SuperRelationship,	Entity2ID, 
							Entity2Type, Entity2Name, StartDate, EndDate, E1_ReadDirect_E2, E1_ReadChild_E2,
							E1_WriteDirect_E2, E1_WriteChild_E2) ' + @BuildSQL

				
				BEGIN TRY
					EXEC dbo.ExecuteRafikiNOSQL @NOSQL=@BuildSQL, @E1=@Entity1ID, @SD=@SD, @ED=@ED
				END TRY
				BEGIN CATCH

				END CATCH
				Fetch NEXT FROM RelationshipCursor INTO   @NEXTREL, @RelName, @SuperRelationship, @E1Type, @E2Type, @RD, @RC, @WD, @WC
	END
	CLOSE RelationshipCursor
	DEALLOCATE RelationshipCursor

	SELECT DISTINCT * FROM #EntityRelationships
	
END